package com.braska.hewankurban.db;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseErrorHandler;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHandler extends SQLiteOpenHelper {

	private static final int DATABASE_VERSION = 13;
	private static final String DATABASE_NAME = "hewankurban";

	private static final String TABLE_PENYAKIT = "Penyakit";
	private static final String KEY_ID_PENYAKIT = "id_penyakit";
	private static final String KEY_KD_PENYAKIT = "kd_penyakit";
	private static final String KEY_PENYAKIT = "penyakit";

	private static final String TABLE_GEJALA = "Gejala";
	private static final String KEY_ID_GEJALA = "id_gejala";
	private static final String KEY_GEJALA = "gejala";
	private static final String KEY_KD_GEJALA = "kd_gejala";

	private static final String TABLE_BOBOT_GEJALA = "Bobot_gejala";
	private static final String KEY_ID_BOBOT_GEJALA = "id_bobot_gejala";
	private static final String KEY_ID_PENYAKIT_FK = "id_penyakit";
	private static final String KEY_ID_GEJALA_FK = "id_gejala";
	private static final String KEY_BOBOT = "bobot";
	private static final String KEY_JENISHEWAN = "jenishewan";


	public DatabaseHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		// TODO Auto-generated constructor stub
		Log.i("db", "constructor");
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub

		Log.i("db", "create db");

		String query_table_penyakit = "CREATE TABLE " + TABLE_PENYAKIT + "("
				+ KEY_ID_PENYAKIT + " INTEGER, " + KEY_KD_PENYAKIT
				+ " VARCHAR, " + KEY_PENYAKIT + " TEXT " + ")";

		String query_table_gejala = "CREATE TABLE " + TABLE_GEJALA + "("
				+ KEY_ID_GEJALA + " INTEGER, " + KEY_GEJALA + " TEXT, "
				+ KEY_KD_GEJALA + " VARCHAR " + ")";

		String query_table_bobot_gejala = "CREATE TABLE " + TABLE_BOBOT_GEJALA
				+ "(" + KEY_ID_BOBOT_GEJALA
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_ID_PENYAKIT_FK
				+ " INTEGER, " + KEY_ID_GEJALA_FK + " INTEGER, " + KEY_BOBOT
				+ " DOUBLE, " + KEY_JENISHEWAN + " VARCHAR " + ")";


		Log.i("db", "query table infus: " + query_table_penyakit);
		Log.i("db", "query table cek: " + query_table_gejala);
		Log.i("db", "query table pasien: " + query_table_bobot_gejala);
		db.execSQL(query_table_penyakit);
		db.execSQL(query_table_gejala);
		db.execSQL(query_table_bobot_gejala);
		Log.i("db", "Database has been created");

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_GEJALA);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_PENYAKIT);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_BOBOT_GEJALA);

		onCreate(db);
	}

	public void tambahgejala(Gejala g) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(KEY_ID_GEJALA, g.id_gejala);
		values.put(KEY_KD_GEJALA, g.kd_gejala);
		values.put(KEY_GEJALA, g.gejala);

		Log.i("db", "addInfus");
		db.insert(TABLE_GEJALA, null, values);
		db.close(); //
	}


	public void tambahpenyakit(Penyakit p) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(KEY_ID_PENYAKIT, p.id_penyakit);
		values.put(KEY_KD_PENYAKIT, p.kd_penyakit);
		values.put(KEY_PENYAKIT, p.penyakit);
		Log.i("db", "addInfus");
		db.insert(TABLE_PENYAKIT, null, values);
		db.close(); //
	}

	public void tambahbobotgejala(Bobot_Penyakit b) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(KEY_ID_BOBOT_GEJALA, b.id_bobotpenyakit);
		values.put(KEY_ID_PENYAKIT_FK, b.id_penyakit);
		values.put(KEY_ID_GEJALA_FK, b.id_gejala);
		values.put(KEY_BOBOT, b.bobot);
		values.put(KEY_JENISHEWAN, b.jenishewan);
		Log.i("db", "addInfus");
		db.insert(TABLE_BOBOT_GEJALA, null, values);
		db.close(); //
	}

	public void deletgejala() {
		Log.d("LOGCAT", "delete");
		SQLiteDatabase db = this.getWritableDatabase();
		String deleteQuery = " Delete from " + TABLE_GEJALA;
		Log.d("query", deleteQuery);
		db.execSQL(deleteQuery);
		db.close();
	}

	public void deletpenyakit() {
		Log.d("LOGCAT", "delete");
		SQLiteDatabase db = this.getWritableDatabase();
		String deleteQuery = " Delete from " + TABLE_PENYAKIT;
		Log.d("query", deleteQuery);
		db.execSQL(deleteQuery);
		db.close();
	}

	public void deletebobotpenyakit() {
		Log.d("LOGCAT", "delete");
		SQLiteDatabase db = this.getWritableDatabase();
		String deleteQuery = " Delete from " + TABLE_BOBOT_GEJALA;
		Log.d("query", deleteQuery);
		db.execSQL(deleteQuery);
		db.close();
	}

	public void getpenyakit(String jenis) {
		SQLiteDatabase db = this.getWritableDatabase();
		Bobot_Penyakit b = new Bobot_Penyakit();
		Penyakit p = new Penyakit();
		Cursor cur = db
				.rawQuery(
						" select * from bobot_gejala join penyakit "
								+ " on bobot_gejala.id_penyakit = penyakit.id_penyakit where bobot_gejala.jenishewan = "
								+ jenis + "", null);

		if (cur.moveToFirst()) {
			do {
				b.setId_bobotpenyakit(cur.getInt(0));
				b.setId_penyakit(cur.getInt(1));
				b.setId_gejala(cur.getInt(2));
				b.setBobot(cur.getDouble(3));
				b.setJenishewan(cur.getString(4));
				p.setId_penyakit(cur.getInt(5));
				p.setKd_penyakit(cur.getString(5));
				p.setPenyakit(cur.getString(6));
			} while (cur.moveToNext());
		}
		cur.close();
		db.close();

	}

	
	public HashMap getpenyakit_from_diagnosis(List<Integer> lst,
			String jenishewan) {
		SQLiteDatabase db = this.getWritableDatabase();
		HashMap perhitungan = new HashMap();
		try {
			String query = "select id_penyakit,penyakit from penyakit";
			// System.out.println(query);
			Cursor cur = db.rawQuery(query, null);
			List<String> penyakit = new ArrayList<String>();
			if (cur.moveToFirst()) {
				do {
					penyakit.add(cur.getString(0));
				} while (cur.moveToNext());
			}
			cur.close();

			double dibagi = 0;
			double pembagi = 0;
			int id_penyakit = 0;

			for (String id_p : penyakit) {
				double nilai = 0;
				// System.out.println("idpenyakitlist: " + id_p);
				String querygetbobot = "select " + KEY_ID_GEJALA_FK + ","
						+ KEY_ID_PENYAKIT_FK + "," + KEY_BOBOT + " from "
						+ TABLE_BOBOT_GEJALA + " where id_penyakit = " + id_p
						+ " and jenishewan = " + jenishewan;
				System.out.println("q: " + querygetbobot);
				Cursor cur2 = db.rawQuery(querygetbobot, null);
				if (cur2.moveToFirst()) {
					do {
						boolean is_dipilih = false;
						for (int id_gejala : lst) {
							System.out.println("idgejala dipilih: " + id_gejala);
							if ((id_gejala + "").equals(cur2.getString(cur2
									.getColumnIndex(KEY_ID_GEJALA_FK)))) {
								is_dipilih = true;
							}
						}
						// System.out.println("isdipilih dalem: " + is_dipilih);
						if (is_dipilih) {
							dibagi = dibagi
									+ (Double.parseDouble(cur2.getString(cur2
											.getColumnIndex(KEY_BOBOT))) * 1);
							
							Log.d("apa ini", dibagi+"");
							Log.d("test", (Double.parseDouble(cur2.getString(cur2
											.getColumnIndex(KEY_BOBOT))) * 1)+"");
						}

						pembagi = pembagi
								+ Double.parseDouble(cur2.getString(cur2
										.getColumnIndex(KEY_BOBOT)));
						System.out.println("dibagi/pembagi: " + dibagi + "/"
								+ pembagi);

					} while (cur2.moveToNext());

				}

				cur2.close();
				// disini nanti perhitungan

				
				nilai = (dibagi / pembagi) * 100;
				System.out.println("nilai " + id_p + ": " + nilai);
				perhitungan.put(id_p, nilai);
				//kembalikan perhitungan masing penyakit
				dibagi = 0;
				pembagi = 0;
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		db.close();
		return perhitungan;
	}

}
